using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text.RegularExpressions;
using SiteManager;

public partial class Ad_Dentist_Upload : System.Web.UI.Page
{
    Manager mgr = new Manager(true,"ConnectionString");
    public DataTable dtDoctors_Err;
    public DataSet ds_common = new DataSet();

    protected void Page_Load(object sender, EventArgs e)
    {
        lblMsg.Text = "";
        ds_common = mgr.ExecuteQuery("GET_SCH_HOS_ORG_DEP", "tbl_common");
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (uplFileUpload.HasFile)
        {
            string contentType = uplFileUpload.PostedFile.ContentType;
            //Response.Write("Content Type:"+contentType);
            string filename = uplFileUpload.PostedFile.FileName;

            // Checking the data for the uploaded file , should be Excel only
            if ((contentType.Equals("application/octet-stream") || contentType.Equals("application/excel") || contentType.Equals("application/vnd.ms-excel") || contentType.Equals("application/x-excel") || contentType.Equals("application/x-msexcel")) && filename.Substring(filename.LastIndexOf('.') + 1).ToUpper().Equals("XLS"))
            {
                // checking whether the file has no data 
                if (uplFileUpload.PostedFile.ContentLength > 0)
                {
                    //  try
                    {
                        // saving the file to the Temporary folder
                        uplFileUpload.SaveAs(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        ExcelReader exReader = new ExcelReader(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        string sheetName = exReader.GetExcelSheetNames()[0];
                        //checking for the correct data and if data is error free inesrting it into the Database
                        //  try
                        {
                            CheckDataToDentistTable(exReader.GetDataTable(sheetName));
                        }
                        //   catch (Exception es)
                        {
                            //     lblMsg.Visible = true;
                            //     lblMsg.Text = es.Message;
                        }
                    }
                    //    catch (Exception ex)
                    {
                        // lblMsg.Visible = true;
                        //lblMsg.Text = ex.Message;
                        // lblMsg.ForeColor = System.Drawing.Color.Red;
                        // return;
                    }
                    //   finally
                    {
                        //Delete File
                        System.IO.FileInfo fi = new System.IO.FileInfo(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        fi.Delete();
                    }
                }
                else
                {
                    lblMsg.Text = "File contains no data!!";
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    return;
                }
            }
            else
            {
                lblMsg.Text = "InCorrect File Type. Please select only XLS file to upload!!";
                lblMsg.ForeColor = System.Drawing.Color.Red;
                return;
            }
        }
        else
        {
            lblMsg.Text = "No uploaded file!!";
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return;
        }
    }

    private void CheckDataToDentistTable(DataTable dt)
    {
        // Table to store error Records in the tables columns if any        
        dtDoctors_Err = new DataTable();

        for (int i = 0; i < dt.Columns.Count; i++)
        { dtDoctors_Err.Columns.Add(dt.Columns[i].ColumnName); }

        bool IsError = false;

        //TWO FIELDS WERE NEWLY ADDED : K , 13 JAN 2007
        //if (dt.Columns.Count == 31)
        if (dt.Columns.Count == 12)
        {
            //reading data for each row of the DataTable (from xls) 
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    bool IsInsertRow = false;
                    //DataRow doctorRow = dt.Rows[i];
                    DataRow doctorRow = dtDoctors_Err.NewRow();
                    for (int ii = 0; ii < dt.Columns.Count; ii++)
                    { doctorRow[ii] = dt.Rows[i][ii]; }

                    # region get each columns the value in varibales
                    string tFName = doctorRow["first_name"].ToString().Trim().Replace("'", "''");
                    string tMName = doctorRow["middle_name"].ToString().Trim().Replace("'", "''");
                    string tLName = doctorRow["last_name"].ToString().Trim().Replace("'", "''");
                    string tEmail = doctorRow["email"].ToString().Trim().Replace("'", "''");
                    string tTitle = doctorRow["title"].ToString().Trim().Replace("'", "''");
                    if (tTitle.Trim().Length < 1) { tTitle = "MD"; }
                    string tGender = string.Empty; // "M";
                    //if (doctorRow["gender"].ToString().Trim().Length > 0)
                    //    tGender = doctorRow["gender"].ToString().Trim().Replace("'", "''").Substring(0, 1);

                    string tLanguages = string.Empty; // doctorRow["languages"].ToString().Trim().Replace("'", "''");
                    string tPhone = doctorRow["phone"].ToString().Trim().Replace("'", "''");
                    string tFax = doctorRow["fax"].ToString().Trim().Replace("'", "''");
                    string tAddress1 = doctorRow["address1"].ToString().Trim().Replace("'", "''");
                    string tAddress2 = string.Empty; // doctorRow["address2"].ToString().Trim().Replace("'", "''");
                    string tCity = doctorRow["city"].ToString().Trim().Replace("'", "''");
                    string tState = doctorRow["state"].ToString().Trim().Replace("'", "''");
                    string tCounty = string.Empty; // doctorRow["county"].ToString().Trim().Replace("'", "''");
                    //string tCountryId = doctorRow["upin"].ToString().Trim().Replace("'", "''");
                    string tZipCode = doctorRow["zip"].ToString().Trim().Replace("'", "''");

                    string tGraduationYear = string.Empty; // doctorRow["graduation_year"].ToString().Trim().Replace("'", "''");
                    string tMedicalSchool = string.Empty; // doctorRow["medical_school"].ToString().Trim().Replace("'", "''");
                    string tHospitalName = string.Empty; // doctorRow["hospital"].ToString().Trim().Replace("'", "''");
                    //string tResidencyTraining = doctorRow["residency_training"].ToString().Trim().Replace("'", "''");
                    string tGroupPractice = string.Empty; // doctorRow["group_practice"].ToString().Trim().Replace("'", "''");
                    //string tProfileDesc = doctorRow["upin"].ToString().Trim().Replace("'", "''");
                    //string tMsa = doctorRow["msa"].ToString().Trim().Replace("'", "''");
                    //string tCmsa = doctorRow["cmsa"].ToString().Trim().Replace("'", "''");

                    //string tAbms_Certified = doctorRow["abms_certification"].ToString().Trim().Replace("'", "''");                    
                    string tAbms_Certified = string.Empty; // doctorRow["certifications"].ToString().Trim().Replace("'", "''");
                    if (tAbms_Certified.Trim().Length > 0)
                        tAbms_Certified = "1";


                    string tSpecialityID = doctorRow["specialty"].ToString().Trim().Replace("'", "''");
                    //string tPrimarySpecialityID = doctorRow["primary_specialty"].ToString().Trim().Replace("'", "''");
                    string tSecondarySpecialityID = string.Empty; // doctorRow["secondary_specialty"].ToString().Trim().Replace("'", "''");

                    //string tUPin = doctorRow["upin"].ToString().Trim().Replace("'", "''");

                    string tMajorActivityID = string.Empty; // doctorRow["major_activity"].ToString().Trim().Replace("'", "''");

                    string tWebsite = string.Empty; // doctorRow["website"].ToString().Trim().Replace("'", "''");

                    ///THESE TWO ARE NEWLY ADDED : K, 13 JAN 2007
                    string tOrganization = string.Empty; // doctorRow["organization"].ToString().Trim().Replace("'", "''");
                    string tDepartment = string.Empty; // doctorRow["department"].ToString().Trim().Replace("'", "''");
                    //K-CODE ENDS 

                    # endregion

                    # region checking for errors in the varibales and setting the suitable error message in the corrresponding Column
                    //checking for errors in the varibales and setting the suitable error message in the corrresponding Column

                    //if (checkLength_DataType(tUPin, "string", 15, 0).Length > 0)
                    //{
                    //    IsInsertRow = true;
                    //    doctorRow.SetColumnError("upin", checkLength_DataType(tUPin, "string", 15, 1));
                    //    doctorRow["upin"] = "<font color='red'>" + doctorRow["upin"] + "</font>";
                    //}
                    //else  // checking for duplicates
                    //{
                    //    if (IsExistUPin(tUPin))
                    //    {
                    //        IsInsertRow = true;
                    //        doctorRow.SetColumnError("upin", "The UPin already exist in the database!!Please try with a different one.");
                    //        doctorRow["upin"] = "<font color='red'>" + doctorRow["upin"] + "</font>";
                    //    }
                    //}

                    if (checkLength_DataType(tFName, "string", 50, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("first_name", checkLength_DataType(tFName, "string", 50, 1));
                        doctorRow["first_name"] = "<font color='red'>" + doctorRow["first_name"] + "</font>";
                    }

                    if (checkLength_DataType(tMName, "string", 50, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("middle_name", checkLength_DataType(tMName, "string", 50, 0));
                        doctorRow["middle_name"] = "<font color='red'>" + doctorRow["middle_name"] + "</font>";
                    }
                    if (checkLength_DataType(tLName, "string", 50, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("last_name", checkLength_DataType(tLName, "string", 50, 1));
                        doctorRow["last_name"] = "<font color='red'>" + doctorRow["last_name"] + "</font>";
                    }
                    if (checkLength_DataType(tTitle, "string", 50, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("title", checkLength_DataType(tTitle, "string", 50, 1));
                        doctorRow["title"] = "<font color='red'>" + doctorRow["title"] + "</font>";
                    }
                    if (checkLength_DataType(tAddress1, "string", 200, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("address1", checkLength_DataType(tAddress1, "string", 200, 0));
                        doctorRow["address1"] = "<font color='red'>" + doctorRow["address1"] + "</font>";
                    }
                    if (checkLength_DataType(tAddress2, "string", 200, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("address2", checkLength_DataType(tAddress2, "string", 200, 0));
                        doctorRow["address2"] = "<font color='red'>" + doctorRow["address2"] + "</font>";
                    }
                    if (checkLength_DataType(tCity, "string", 50, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("city", checkLength_DataType(tCity, "string", 50, 1));
                        doctorRow["city"] = "<font color='red'>" + doctorRow["city"] + "</font>";
                    }
                    if (checkLength_DataType(tState, "string", 50, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("state", checkLength_DataType(tState, "string", 50, 1));
                        doctorRow["state"] = "<font color='red'>" + doctorRow["state"] + "</font>";
                    }
                    else
                    {
                        if (!IsExistState(tState))
                        {
                            IsInsertRow = true;
                            doctorRow.SetColumnError("state", "This state does not exist in the States table");
                            doctorRow["state"] = "<font color='red'>" + doctorRow["state"] + "</font>";
                        }
                    }

                    if (checkLength_DataType(tZipCode, "string", 10, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("zip", checkLength_DataType(tZipCode, "string", 10, 1));
                        doctorRow["zip"] = "<font color='red'>" + doctorRow["zip"] + "</font>";
                    }
                    if (checkLength_DataType(tCounty, "string", 100, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("county", checkLength_DataType(tCounty, "string", 100, 0));
                        doctorRow["county"] = "<font color='red'>" + doctorRow["county"] + "</font>";
                    }
                    //if (checkLength_DataType(tGender, "string", 10, 1).Length > 0)
                    //{
                    //    IsInsertRow = true;
                    //    doctorRow.SetColumnError("gender", checkLength_DataType(tGender, "string", 10, 0));
                    //    doctorRow["gender"] = "<font color='red'>" + doctorRow["gender"] + "</font>";
                    //}
                    if (checkLength_DataType(tMedicalSchool, "string", 1000, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("medical_school", checkLength_DataType(tMedicalSchool, "string", 1000, 0));
                        doctorRow["medical_school"] = "<font color='red'>" + doctorRow["medical_school"] + "</font>";
                    }
                    //if (checkLength_DataType(tResidencyTraining, "string", 5000, 0).Length > 0)
                    //{
                    //    IsInsertRow = true;
                    //    doctorRow.SetColumnError("residency_training", checkLength_DataType(tResidencyTraining, "string", 5000, 0));
                    //    doctorRow["residency_training"] = "<font color='red'>" + doctorRow["residency_training"] + "</font>";
                    //}
                    if (checkLength_DataType(tPhone, "string", 50, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("phone", checkLength_DataType(tPhone, "string", 50, 0));
                        doctorRow["phone"] = "<font color='red'>" + doctorRow["phone"] + "</font>";
                    }
                    if (checkLength_DataType(tFax, "string", 20, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("fax", checkLength_DataType(tFax, "string", 20, 0));
                        doctorRow["fax"] = "<font color='red'>" + doctorRow["fax"] + "</font>";
                    }

                    if (checkLength_DataType(tEmail, "string", 100, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("email", checkLength_DataType(tEmail, "string", 100, 0));
                        doctorRow["email"] = "<font color='red'>" + doctorRow["email"] + "</font>";
                    }
                    else if (tEmail.Trim().Length > 0)
                    {
                        //testing for a valid email
                        if (!IsEmail(tEmail))
                        {
                            IsInsertRow = true;
                            doctorRow.SetColumnError("email", "Not a valid email.");
                            doctorRow["email"] = "<font color='red'>" + doctorRow["email"] + "</font>";
                        }
                        else
                        {
                            //check for duplicates email in the database                            
                            if (IsExistEmail(tEmail))
                            {
                                IsInsertRow = true;
                                doctorRow.SetColumnError("email", "The email already exist in the database!!Please try with a different email.");
                                doctorRow["email"] = "<font color='red'>" + doctorRow["email"] + "</font>";
                            }

                        }
                    }

                    if (checkLength_DataType(tWebsite, "string", 200, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("website", checkLength_DataType(tWebsite, "string", 200, 0));
                        doctorRow["website"] = "<font color='red'>" + doctorRow["website"] + "</font>";
                    }

                    if (checkLength_DataType(tSpecialityID, "string", 500, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("specialty", checkLength_DataType(tSpecialityID, "string", 500, 0));
                        doctorRow["specialty"] = "<font color='red'>" + doctorRow["specialty"] + "</font>";
                    }
                    else
                    {
                        if (!IsExistSpecialtyId(tSpecialityID, 0))
                        {
                            IsInsertRow = true;
                            doctorRow.SetColumnError("specialty", "This specialty does not exist in the Specialty table");
                            doctorRow["specialty"] = "<font color='red'>" + doctorRow["specialty"] + "</font>";
                        }
                    }

                    //if (checkLength_DataType(tPrimarySpecialityID, "string", 500, 0).Length > 0)
                    //{
                    //    IsInsertRow = true;
                    //    doctorRow.SetColumnError("primary_specialty", checkLength_DataType(tPrimarySpecialityID, "string", 500, 0));
                    //    doctorRow["primary_specialty"] = "<font color='red'>" + doctorRow["primary_specialty"] + "</font>";
                    //}
                    //else
                    //{
                    //    if (tPrimarySpecialityID.Trim().Length > 0)
                    //    {
                    //        if (!IsExistSpecialtyId(tPrimarySpecialityID, 1))
                    //        {
                    //            IsInsertRow = true;
                    //            doctorRow.SetColumnError("primary_specialty", "This specialty does not exist in the Primary Specialty table");
                    //            doctorRow["primary_specialty"] = "<font color='red'>" + doctorRow["primary_specialty"] + "</font>";
                    //        }
                    //    }
                    //}

                    if (checkLength_DataType(tSecondarySpecialityID, "string", 500, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("secondary_specialty", checkLength_DataType(tSecondarySpecialityID, "string", 500, 0));
                        doctorRow["secondary_specialty"] = "<font color='red'>" + doctorRow["secondary_specialty"] + "</font>";
                    }
                    else
                    {
                        if (tSecondarySpecialityID.Trim().Length > 0)
                        {
                            if (!IsExistSpecialtyId(tSecondarySpecialityID, 2))
                            {
                                IsInsertRow = true;
                                doctorRow.SetColumnError("secondary_specialty", "This specialty does not exist in the Secondary Specialty table");
                                doctorRow["secondary_specialty"] = "<font color='red'>" + doctorRow["secondary_specialty"] + "</font>";
                            }
                        }
                    }

                    if (checkLength_DataType(tMajorActivityID, "string", 300, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("major_activity", checkLength_DataType(tMajorActivityID, "string", 300, 0));
                        doctorRow["major_activity"] = "<font color='red'>" + doctorRow["major_activity"] + "</font>";
                    }
                    else
                    {
                        if (tMajorActivityID.Trim().Length > 0)
                        {
                            if (!IsExistActivityId(tMajorActivityID))
                            {
                                IsInsertRow = true;
                                doctorRow.SetColumnError("major_activity", "The activity does not exist in the Major Activity table.");
                                doctorRow["major_activity"] = "<font color='red'>" + doctorRow["major_activity"] + "</font>";
                            }
                        }
                    }

                    if (checkLength_DataType(tHospitalName, "string", 1000, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("hospital", checkLength_DataType(tHospitalName, "string", 1000, 0));
                        doctorRow["hospital"] = "<font color='red'>" + doctorRow["hospital"] + "</font>";
                    }
                    if (checkLength_DataType(tGroupPractice, "string", 1000, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("group_practice", checkLength_DataType(tGroupPractice, "string", 1000, 0));
                        doctorRow["group_practice"] = "<font color='red'>" + doctorRow["group_practice"] + "</font>";
                    }

                    if (checkLength_DataType(tAbms_Certified, "string", 1000, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        //doctorRow.SetColumnError("abms_certification", checkLength_DataType(tAbms_Certified, "string", 1000, 0));
                        doctorRow.SetColumnError("certifications", checkLength_DataType(tAbms_Certified, "string", 1000, 0));
                        //doctorRow["abms_certification"] = "<font color='red'>" + doctorRow["abms_certification"] + "</font>";
                        doctorRow["certifications"] = "<font color='red'>" + doctorRow["certifications"] + "</font>";
                    }

                    if (checkLength_DataType(tLanguages, "string", 200, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("languages", checkLength_DataType(tLanguages, "string", 200, 0));
                        doctorRow["languages"] = "<font color='red'>" + doctorRow["languages"] + "</font>";
                    }
                    //if (checkLength_DataType(tMsa, "string", 10, 0).Length > 0)
                    //{
                    //    IsInsertRow = true;
                    //    doctorRow.SetColumnError("msa", checkLength_DataType(tMsa, "string", 10, 0));
                    //    doctorRow["msa"] = "<font color='red'>" + doctorRow["msa"] + "</font>";
                    //}
                    //if (checkLength_DataType(tCmsa, "string", 10, 0).Length > 0)
                    //{
                    //    IsInsertRow = true;
                    //    doctorRow.SetColumnError("cmsa", checkLength_DataType(tCmsa, "string", 10, 0));
                    //    doctorRow["cmsa"] = "<font color='red'>" + doctorRow["cmsa"] + "</font>";
                    //}

                    if (checkLength_DataType(tGraduationYear, "int", 0, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("graduation_year", checkLength_DataType(tGraduationYear, "int", 0, 0));
                        doctorRow["graduation_year"] = "<font color='red'>" + doctorRow["graduation_year"] + "</font>";
                    }

                    //THIS TWO VALIDATION BLOCKS ARE NEWLY ADDED : K, 13 JAN 2007
                    if (checkLength_DataType(tOrganization, "string", 200, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("organization", checkLength_DataType(tOrganization, "string", 200, 0));
                        doctorRow["organization"] = "<font color='red'>" + doctorRow["organization"] + "</font>";
                    }
                    if (checkLength_DataType(tDepartment, "string", 80, 0).Length > 0)
                    {
                        IsInsertRow = true;
                        doctorRow.SetColumnError("department", checkLength_DataType(tDepartment, "string", 80, 0));
                        doctorRow["department"] = "<font color='red'>" + doctorRow["department"] + "</font>";
                    }
                    //K-CODE ENDS
                    # endregion

                    # region Insert error rows if any in error datatable
                    if (IsInsertRow)
                    {
                        //dtDoctors_Err.ImportRow(doctorRow);
                        dtDoctors_Err.Rows.Add(doctorRow);

                        IsError = true;
                    }
                    # endregion
                }

                #region Final Action
                if (IsError)
                {
                    //Show the rows containing errors in the DataGrid  
                    grdErrors.Visible = true;
                    grdErrors.DataSource = dtDoctors_Err;
                    grdErrors.DataBind();

                    lblMsg.Text = "Some rows in the XLS file contains errors!!<br>Please see the error rows (Columns in red color) in the listing below.<br>To view error details place your mouse over the red area<br>Please correct the errors and upload the file again.";
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    return;
                }
                else
                {
                    #region Insert the XLS Records into the database
                    //Insert the records into database 

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow doctorRow = dt.Rows[i];

                        # region get each columns value in varibales                -- ok 
                        string tFName = doctorRow["first_name"].ToString().Trim(); 
                        string tMName = doctorRow["middle_name"].ToString();                        
                        string tLName = doctorRow["last_name"].ToString().Trim();
                        string tEmail = doctorRow["email"].ToString().Trim();
                        string tTitle = doctorRow["title"].ToString().Trim();
                        if (tTitle.Trim().Length < 1) { tTitle = "MD"; }
                        string tGender = "M";
                        //if (doctorRow["gender"].ToString().Trim().Length > 0)
                        //    tGender = doctorRow["gender"].ToString().Trim().Replace("'", "''").Substring(0, 1);

                        string tLanguages = string.Empty; //doctorRow["languages"].ToString().Trim(); 
                        string tPhone = doctorRow["phone"].ToString().Trim(); 
                        string tFax = doctorRow["fax"].ToString().Trim(); 
                        string tAddress1 = doctorRow["address1"].ToString().Trim();
                        string tAddress2 = string.Empty; //doctorRow["address2"].ToString().Trim();
                        string tCity = doctorRow["city"].ToString().Trim(); 

                        string tState = doctorRow["state"].ToString().Trim();

                        string tCounty = string.Empty; //doctorRow["county"].ToString().Trim();                        
                        string tZipCode = doctorRow["zip"].ToString().Trim();

                        string tGraduationYear = string.Empty; //DateTime.Now.Year.ToString();
                        //if (doctorRow["graduation_year"].ToString().Trim().Length > 3)
                        //    tGraduationYear = doctorRow["graduation_year"].ToString().Trim();

                        string tMedicalSchool = string.Empty; //doctorRow["medical_school"].ToString().Trim();
                        string tHospitalName = string.Empty; //doctorRow["hospital"].ToString().Trim(); 
                        string tResidencyTraining = string.Empty; //doctorRow["residency_training"].ToString().Trim();
                        string tGroupPractice = string.Empty; //doctorRow["group_practice"].ToString().Trim();                        
                        //string tMsa = doctorRow["msa"].ToString().Trim(); 
                        //string tCmsa = doctorRow["cmsa"].ToString().Trim();

                        string tAbms_Certified = string.Empty; //doctorRow["certifications"].ToString().Trim();
                        if (tAbms_Certified.Length > 0)
                            tAbms_Certified = "1";
                        else
                            tAbms_Certified = "0";

                        string tSpecialityID = doctorRow["specialty"].ToString().Trim(); 
                        //string tPrimarySpecialityID = doctorRow["primary_specialty"].ToString().Trim(); 
                        string tSecondarySpecialityID = string.Empty; //doctorRow["secondary_specialty"].ToString().Trim(); 

                        //Gets the corresponding Specialty id for the Specialty text
                        tSpecialityID = GetSpecialtyId(tSpecialityID, 0);

                        //tPrimarySpecialityID = GetSpecialtyId(tPrimarySpecialityID, 1);
                        tSecondarySpecialityID = GetSpecialtyId(tSecondarySpecialityID, 2); 

                        //string tUPin = doctorRow["upin"].ToString().Trim().Replace("'", "''");

                        string tMajorActivityID = "0"; //doctorRow["major_activity"].ToString().Trim();

                        //Gets the corresponding Activity id for the Activity
                        //tMajorActivityID = GetActivityId(tMajorActivityID);

                        string tWebsite = string.Empty; //doctorRow["website"].ToString().Trim();

                        //THESE TWO FIELDS ARE NEWLY ADDED : K, 13 JANUARY 2007
                        string tOrganization = string.Empty; //doctorRow["organization"].ToString().Trim();
                        string tDepartment = string.Empty; //doctorRow["department"].ToString().Trim(); 
                        //K-CODE ENDS
                        # endregion

                        string tPassword = "";
                        string tCountryId = "US";

                        # region Insert the rows into the database

                        string[] aMedSch;
                        string Medicalschid = "";
                        string Residencyid = "";
                        string[] aRes;
                        char[] splitter = { ';' };
                        aMedSch = tMedicalSchool.Split(splitter);
                        aRes = tResidencyTraining.Split(splitter);

                        #region Inserting medical School                -- ok 

                        for (int i1 = 0; i1 < aMedSch.Length; i1++)
                        {
                            //OBSOLETE  : K, 19 JANUARY 2007

                            //COMMON DATASET IS TO BE USED FROM NOW : K, 19, JANUARY 2007
                            DataRow[] rr = ds_common.Tables[1].Select("MedicalSchool='" + aMedSch[i1].ToString().Trim().Replace("'", "''") + "'");
                            int len = rr.Length;
                            if (len > 0) // med sch exists
                            {                                
                                Medicalschid = rr[0][0].ToString();
                                break;
                            }
                            else   // add new Med school
                            {
                                int m_id = insert_medicalschool(aMedSch[i1].ToString().Trim());                                
                                Medicalschid = m_id.ToString();
                                break;
                            }
                        }

                        #endregion

                        #region LOGICAL ERROR FOUND. RESIDENCY IS RELATED TO HOSPITALS NOT MEDICAL SCHOOL - GARBAGE : K, 15 JANUARY 2007
                        //for (int i1 = 0; i1 < ares.Length; i1++)
                        //{
                        //    DataRow[] rr = ds_medsch.Tables[0].Select("MedicalSchool='" + ares[i1].ToString().Trim().Replace("'", "''") + "'");
                        //    int len = rr.Length;
                        //    if (len > 0) // med sch exists
                        //    {
                        //        //Residencyid = Residencyid + rr[0][0] + ",";
                        //        Residencyid = rr[0][0].ToString();
                        //        break;
                        //    }
                        //    else   // add new Med school
                        //    {
                        //        int m_id = insert_medicalschool(ares[i1].ToString().Trim());
                        //        //Residencyid = Residencyid + m_id + ",";
                        //        Residencyid = m_id.ToString() ;
                        //        break;
                        //    }
                        //}
                        #endregion

                        #region Inserting Residency : K, 15 JANAURY 2007                -- ok 

                        for (int i1 = 0; i1 < aRes.Length; i1++)
                        {
                            //OBSOLETE  : K, 19 JANUARY 2007
                            //DataRow[] rr = ds_hos.Tables[0].Select("HName='" + aRes[i1].ToString().Trim().Replace("'", "''") + "'");

                            //COMMON DATASET IS TO BE USED FROM NOW : K, 19, JANUARY 2007
                            DataRow[] rr = ds_common.Tables[0].Select("HName='" + aRes[i1].ToString().Trim().Replace("'", "''") + "'");
                            int len = rr.Length;
                            if (len > 0) // hospital exists
                            {
                                Residencyid = rr[0]["id"].ToString();
                                break;
                            }
                            else
                            {   // add new hospital
                                if (aRes[i1].ToString().Trim().Length > 0)
                                {
                                    //string sqlQuery1 = "";
                                    SqlParameter[] up_hospital_prm = new SqlParameter[2];
                                    up_hospital_prm[0] = new SqlParameter("@HOSPITAL_NAME", SqlDbType.VarChar, 500);
                                    up_hospital_prm[1] = new SqlParameter("@HOSPITAL_ID", SqlDbType.Int);
                                    up_hospital_prm[1].Direction = ParameterDirection.Output;
                                    try
                                    {
                                        //K-CODE BEGIN
                                        up_hospital_prm[0].Value = aRes[i1].ToString().Trim();
                                        mgr.ExecuteUpdate("ADD_UPDATE_BULK_HOSPITAL", up_hospital_prm);
                                        Residencyid = up_hospital_prm[1].Value.ToString();
                                        //K-CODE ENDS
                                    }
                                    catch (Exception ex)
                                    {
                                        lblMsg.Visible = true;
                                        lblMsg.Text = ex.ToString();
                                        lblMsg.ForeColor = System.Drawing.Color.Red;
                                        return;
                                    }
                                    ds_common = mgr.ExecuteQuery("GET_SCH_HOS_ORG_DEP", "tbl_common");
                                }
                                break;
                            }
                        }
                        #endregion

                        #region old code - garbage              -- n/a 
                        //THIS QUERY HAS BEEN REPLACED BY SQL STORED PROCEDURE, K: 13 JAN 2007

                        //sqlQuery = "SET NOCOUNT ON ";
                        //sqlQuery += "insert into tbl_doctors(UPin,SpecialityID,PrimarySpecialityID,SecondarySpecialityID,";
                        //sqlQuery += "Abms_CertificationID,FName,MName,LName,Email,Title,Password,Phone,Fax";
                        //sqlQuery += ",Address1,Address2,City,State,CountryId,Languages,County,Msa,Cmsa,";
                        //sqlQuery += "ZipCode,Gender,MedicalSchool,GraduationYear,HospitalName,MajorActivityID,";
                        //sqlQuery += "Website,ResidencyTraining,GroupPractice,IsBulk)values('";
                        //sqlQuery += tUPin + "'," + tSpecialityID + "," + tPrimarySpecialityID + "," + tSecondarySpecialityID + ",'" 
                        //    + tAbms_Certified + "','" + tFName + "','" + tMName + "','" + tLName + "','" + tEmail + "','" 
                        //    + tTitle + "','" + tPassword + "','" + tPhone + "','" + tFax + "','" + tAddress1 + "','" + tAddress2 
                        //    + "','" + tCity + "','" + tState + "','" + tCountryId + "','" + tLanguages + "','" + tCounty + "','" 
                        //    + tMsa + "','" + tCmsa + "','" + tZipCode + "','" + tGender + "','" + Medicalschid + "'," 
                        //    + tGraduationYear + ",'" + tHospitalName + "'," + tMajorActivityID + ",'" + tWebsite + "','" 
                        //    + Residencyid + "','" + tGroupPractice + "','1')";
                        //sqlQuery += "Select @@IDENTITY as Dr_ID";

                        //FIELD 'HOSPITAL' WAS REMOVED, and
                        //'ORGANIZATION' AND 'DEPARTMENT' FIELD WERE ADDED.
                        //: K, 13 JANUARY 2007
                        #endregion

                        #region PARAMETER DESCRIPTION                   -- ok 
                        SqlParameter[] prm = new SqlParameter[28];
                        prm[0] = new SqlParameter("@SPECIALITYID", SqlDbType.Int);
                        prm[1] = new SqlParameter("@SECONDARYSPECIALITYID", SqlDbType.Int);
                        prm[2] = new SqlParameter("@ABMS_CERTIFIED", SqlDbType.Bit);
                        prm[3] = new SqlParameter("@FNAME", SqlDbType.VarChar, 50);
                        prm[4] = new SqlParameter("@MNAME", SqlDbType.VarChar, 50);
                        prm[5] = new SqlParameter("@LNAME", SqlDbType.VarChar, 50);
                        prm[6] = new SqlParameter("@EMAIL", SqlDbType.VarChar, 100);
                        prm[7] = new SqlParameter("@TITLE", SqlDbType.VarChar, 50);
                        prm[8] = new SqlParameter("@PASSWORD", SqlDbType.VarChar, 15);
                        prm[9] = new SqlParameter("@PHONE", SqlDbType.VarChar, 50);
                        prm[10] = new SqlParameter("@FAX", SqlDbType.VarChar, 20);
                        prm[11] = new SqlParameter("@ADDRESS1", SqlDbType.VarChar, 200);
                        prm[12] = new SqlParameter("@ADDRESS2", SqlDbType.VarChar, 200);
                        prm[13] = new SqlParameter("@CITY", SqlDbType.VarChar, 50);
                        prm[14] = new SqlParameter("@STATE", SqlDbType.VarChar, 2);
                        prm[15] = new SqlParameter("@COUNTRYID", SqlDbType.VarChar, 10);
                        prm[16] = new SqlParameter("@LANGUAGES", SqlDbType.VarChar, 200);
                        prm[17] = new SqlParameter("@COUNTY", SqlDbType.VarChar, 100);
                        prm[18] = new SqlParameter("@ZIPCODE", SqlDbType.VarChar, 10);
                        prm[19] = new SqlParameter("@GENDER", SqlDbType.VarChar, 2);
                        prm[20] = new SqlParameter("@MEDICALSCHID", SqlDbType.Int);
                        prm[21] = new SqlParameter("@GRADUATIONYEAR", SqlDbType.Int);
                        prm[22] = new SqlParameter("@MAJORACTIVITYID", SqlDbType.Int);
                        prm[23] = new SqlParameter("@WEBSITE", SqlDbType.VarChar, 200);                        
                        prm[24] = new SqlParameter("@GROUPPRACTICE", SqlDbType.VarChar, 1000);
                        prm[25] = new SqlParameter("@Dnt_ID", SqlDbType.Int);
                        prm[25].Direction = ParameterDirection.Output;
                        prm[26] = new SqlParameter("@ORGANIZATION", SqlDbType.VarChar, 200);
                        prm[27] = new SqlParameter("@DEPARTMENT", SqlDbType.VarChar, 80);
                        #endregion

                        #region PARAMETER FILLING               -- ok 
                        prm[0].Value = Convert.ToInt32(tSpecialityID);
                        prm[1].Value = Convert.ToInt32(tSecondarySpecialityID);
                        prm[2].Value = ((tAbms_Certified == "1") ? true : false);
                        prm[3].Value = tFName;
                        prm[4].Value = tMName;
                        prm[5].Value = tLName;
                        prm[6].Value = tEmail;
                        prm[7].Value = tTitle;
                        prm[8].Value = tPassword;
                        prm[9].Value = tPhone;
                        prm[10].Value = tFax;
                        prm[11].Value = tAddress1;
                        prm[12].Value = tAddress2;
                        prm[13].Value = tCity;
                        prm[14].Value = tState;
                        prm[15].Value = tCountryId;
                        prm[16].Value = tLanguages;
                        prm[17].Value = tCounty;
                        prm[18].Value = tZipCode;
                        prm[19].Value = tGender;
                        prm[20].Value = ((Medicalschid.Length==0)?0:Convert.ToInt32(Medicalschid));
                        prm[21].Value = ((tGraduationYear.Length>0)?Convert.ToInt32(tGraduationYear):0);
                        prm[22].Value = Convert.ToInt32(tMajorActivityID);
                        prm[23].Value = tWebsite;                        
                        prm[24].Value = tGroupPractice;
                        prm[26].Value = tOrganization;
                        prm[27].Value = tDepartment;
                        #endregion

                        int Dnt_id;

                        #region Inserting Dentist               -- ok 

                        try
                        {
                            //   THIS QUERY HAS BEEN REPLACED BY STORED PROCEDURE :K,13 JANUARY 2007
                            //ds_id = mgr.ExecuteQuery(sqlQuery, "tbl");

                            //needs to be changed
                            mgr.ExecuteUpdate("INSERT_BULK_DENTIST", prm);
                            Dnt_id = Convert.ToInt32(prm[25].Value);
                        }
                        catch (Exception ex)
                        {
                            lblMsg.Visible = true;
                            lblMsg.Text = ex.ToString();
                            lblMsg.ForeColor = System.Drawing.Color.Red;
                            return;
                        }

                        #endregion

                        #region Inserting Hospital Relation  (Majorly Editted)   : K, 13 January 2007               -- ok 

                        string[] aHospitalName;
                        char[] splitc = { ';' };
                        aHospitalName = tHospitalName.Split(splitc);
                        string hospID = "";
                        for (int i2 = 0; i2 < aHospitalName.Length; i2++)
                        {
                            //COMMON DATASET IS TO BE USED FROM NOW : K, 19 JANUARY 2007
                            DataRow[] rr2 = ds_common.Tables[0].Select("HName='" + aHospitalName[i2].ToString().Trim().Replace("'", "''") + "'");

                            if (rr2.Length > 0) // hospital exists
                            {
                                try
                                {
                                    hospID = rr2[0]["id"].ToString();
                                }
                                catch (Exception ex)
                                {
                                    lblMsg.Visible = true;
                                    lblMsg.Text = ex.ToString();
                                    lblMsg.ForeColor = System.Drawing.Color.Red;
                                    return;
                                }
                            }
                            else   // add new hospital
                            {
                                int h_id = insert_Hospital(aHospitalName[i2].ToString().Trim());
                                hospID = h_id.ToString();
                                ds_common = mgr.ExecuteQuery("GET_SCH_HOS_ORG_DEP", "tbl_common");
                            }

                            string strH = "select count(id) from tbl_Dentist_Hospital where dnt_id=" + Dnt_id.ToString() + " and hospital_id=" + hospID.ToString() + " and isdelete=0";
                            object o = mgr.ExecuteScalarQuery(strH);
                            if ((int)o <= 0)
                            {
                                mgr.ExecuteUpdate("insert into tbl_Dentist_Hospital (dnt_id,Hospital_Id,isDelete) values("
                                                + Dnt_id.ToString() + "," + hospID.ToString() + ",0);");
                            }
                        }

                        #endregion

                        #region Department : K, 19 January 2007             -- n/a 
                        ////kajal
                        //string[] aDepartment;
                        //string DeptId = "";
                        //char[] dept_splitter = { ';' };
                        //aDepartment = tDepartment.Split(dept_splitter);
                        //for (int i1 = 0; i1 < aDepartment.Length; i1++)
                        //{
                        //    //OBSOLETE : K, 19 JANUARY 2007
                        //    //DataRow[] rr = ds_medsch.Tables[0].Select("Department='" + aMedSch[i1].ToString().Trim().Replace("'", "''") + "'");

                        //    //COMMON DATASET IS TO BE USED NOW : K, 19 JANUARY 2007
                        //    DataRow[] rr3 = ds_common.Tables[2].Select("Department='"
                        //        + aDepartment[i1].ToString().Trim().Replace("'", "''") + "'");
                        //    int len = rr3.Length;
                        //    if (len > 0) // Department exists
                        //    {
                        //        DeptId = rr3[0][0].ToString();
                        //        //break;
                        //    }
                        //    else   // add new Department
                        //    {
                        //        int dpt_id = insert_Department(aDepartment[i1].ToString().Trim());
                        //        DeptId = dpt_id.ToString();
                        //        //break;
                        //    }

                        //    mgr.ExecuteUpdate("insert into tbl_doctor_departments (Dr_id,DeptID) values ("
                        //        + Dr_id.ToString() + "," + DeptId.ToString() + ");");
                        //}

                        #endregion

                        #region Organization : K, 19 January 2007               -- n/a 
                        ////kajal
                        //string[] aOrganization;
                        //string OrgId = "";
                        //char[] org_splitter = { ';' };
                        //aOrganization = tOrganization.Split(org_splitter);
                        //for (int i1 = 0; i1 < aOrganization.Length; i1++)
                        //{
                        //    //OBSOLETE : K, 19 JANUARY 2007
                        //    //DataRow[] rr = ds_medsch.Tables[0].Select("MedicalSchool='" + aMedSch[i1].ToString().Trim().Replace("'", "''") + "'");

                        //    //COMMON DATASET IS TO BE USED NOW : K, 19 JANUARY 2007                                
                        //    DataRow[] rr4 = ds_common.Tables[2].Select("Organization='"
                        //        + aOrganization[i1].ToString().Trim().Replace("'", "''") + "'");
                        //    int len = rr4.Length;
                        //    if (len > 0) // organization exists
                        //    {
                        //        OrgId = rr4[0][0].ToString();
                        //        //break;
                        //    }
                        //    else   // add new organization
                        //    {
                        //        //int m_id = insert_medicalschool(aMedSch[i1].ToString().Trim());
                        //        int o_id = insert_Organization(aOrganization[i1].ToString().Trim());
                        //        OrgId = o_id.ToString();
                        //        //break;
                        //    }

                        //    mgr.ExecuteUpdate("insert into tbl_doctor_organizations (Dr_id,OrgID) values ("
                        //        + Dr_id.ToString() + "," + OrgId.ToString() + ");");
                        //}

                        #endregion

                    }
                        # endregion //query to insert rows in database
                    
                    lblMsg.Text = "File " + uplFileUpload.FileName.ToString() + " uploaded successfully..";
                    lblMsg.ForeColor = System.Drawing.Color.Green;
                    grdErrors.Visible = false;

                    # endregion     // insert xls
                }
                #endregion // final action
            }
            else
            {
                //No rows found in the table
                lblMsg.Text = "No Data!!";
                lblMsg.ForeColor = System.Drawing.Color.Red;
                return;
            }
        }
        else
        {
            //Wrong no. of Columns
            lblMsg.Text = "Wrong no. of Columns!!";
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return;
        }
    }

    /// <summary>
    /// This function checks for the DataType and Length of the var variable
    /// </summary>
    /// <param name="var">the parameter whose value has to be checked</param>
    /// <param name="dataType">DataType contains the datatype string to chcked against </param>
    /// <param name="maxLen">Parameter var maximum length should be less than maxLen</param>
    /// <returns></returns>
    private string checkLength_DataType(string var, string dataType, int maxLen, int required)
    {
        if (dataType == "string")
        {
            if (required == 1)
            {
                if (var.Trim().Length <= 0)
                    return "The value of this column can not be empty!!";
            }

            if (var.Length > maxLen)
                return "Maximium length of this field should be <=" + maxLen.ToString();
        }
        else if (dataType == "int")
        {
            if (required == 1)
            {
                if (var.Trim().Length <= 0)
                    return "The value of this column can not be empty!!";
            }
            try
            {
                if (var.Trim().Length > 0)
                {
                    int test = Convert.ToInt32(var);
                }
            }
            catch
            {
                return " DataType MisMatch. Integer Type is required!!";
            }
        }
        return "";
    }

    /// <summary>
    /// This Function test for a valid Email and return true or false. 
    /// </summary>
    /// <param name="inputEmail">parameter to be checked for a vliad email</param>
    /// <returns></returns>
    public bool IsEmail(String inputEmail)
    {
        string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" + @"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" + @".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
        Regex re = new Regex(strRegex);
        if (re.IsMatch(inputEmail))
            return (true);
        else
            return (false);
    }

    public bool IsExistEmail(String inputEmail)
    {
        string sql = "select count(*) from tbl_doctors where email='" + inputEmail.Trim() + "' and isdelete=0";
        DataSet ds = mgr.ExecuteQuery(sql, "tbl");
        int count = (int)ds.Tables[0].Rows[0][0];
        if (count > 0)
            return true;
        else
            return false;
    }

    /// <summary>
    /// IsExistSpecialtyId function checks for the existence of the passed specialty in the specialty,Primary
    /// Specialty or Secondary specialty tables and returns a true or false value.
    /// </summary>
    /// <param name="specialty">specialty name</param>
    /// <param name="SpecialtyType">if it contains 0 means search in the specialty table,1 means in 
    /// P.Specialty table, 2 means in S.Specialty table.
    /// </param>
    /// <returns></returns>
    private bool IsExistSpecialtyId(string specialty, int SpecialtyType)
    {///The following 3 specialty are supposed to be same, hence client had asked to keep the following mentioned names
        ///editted: 14 Feb 2007, K
        //switch (specialty.ToUpper())
        //{
        //    case "GERIATRIC MEDICINE":
        //        specialty = "Geriatrics";
        //        break;
        //    //case "INFECTIOUS DISEASE":
        //    //  specialty = "Infectious Disease/Infectious";
        //    //break;
        //    case "SURGERY, THOR SURG":
        //        specialty = "Thoracic Surgery";
        //        break;
        //}
        //k-code ends
        string sql = "";
        if (SpecialtyType == 0)
            sql = "select count(*) from tbl_DentistSpecialty where Specialty='" + specialty.Trim() + "'";
        //else if (SpecialtyType == 1)
        //    sql = "select count(*) from tbl_PrimarySpeciality where name='" + specialty.Trim() + "'";
        else if (SpecialtyType == 2)
            sql = "select count(*) from tbl_DentistSubSpecialty where Specialty='" + specialty.Trim() + "'";
        //Response.Write(sql + "<br>");
        
        try
        {
            DataSet ds = mgr.ExecuteQuery(sql, "tblSpecialty");
            int count = (int)ds.Tables[0].Rows[0][0];
            if (count > 0)
                return true;
            else
                return false;
        }
        catch { return false; }
    }

    private string GetSpecialtyId(string specialty, int SpecialtyType)
    {
        if (specialty.Trim().Length <= 0)
        { return "0"; }

        string sql = "";

        ///The following 3 specialty are supposed to be same, hence client had asked to keep the following mentioned names
        ///editted: 14 Feb 2007, K
        //switch (specialty.ToUpper())
        //{
        //    case "GERIATRIC MEDICINE":
        //        specialty = "Geriatrics";
        //        break;
        //    //  case "INFECTIOUS DISEASE":
        //    //    specialty = "Infectious Disease/Infectious";
        //    //  break;
        //    case "SURGERY, THOR SURG":
        //        specialty = "Thoracic Surgery";
        //        break;
        //}
        //k-code ends

        if (SpecialtyType == 0)
            sql = "select id from tbl_DentistSpecialty where specialty='" + specialty.Trim() + "'";
        //else if (SpecialtyType == 1)
        //    sql = "select id from tbl_PrimarySpeciality where name='" + specialty.Trim() + "'";
        else if (SpecialtyType == 2)
            sql = "select id from tbl_DentistSubSpecialty where specialty='" + specialty.Trim() + "'";

        try
        {
            DataSet ds = mgr.ExecuteQuery(sql, "tblSpl");
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString().Trim();
            }
        }
        catch { return "0"; }

        return "0";
    }

    /// <summary>
    /// Checks whether the specified stateId exists in the DataBase.
    /// </summary>
    /// <param name="specialty"></param>
    /// <param name="SpecialtyType"></param>
    /// <returns></returns>
    private bool IsExistState(string state)
    {
        string sql = "";
        sql = "select count(*) from tbl_States where StateId='" + state.Trim() + "'";

        try
        {
            DataSet ds = mgr.ExecuteQuery(sql, "tblStates");
            int count = (int)ds.Tables[0].Rows[0][0];
            if (count > 0)
                return true;
            else
                return false;
        }
        catch { return false; }
    }

    /// <summary>
    /// Checks for the existence of the specified Activity in the tbl_activity table and return True or False 
    /// </summary>
    /// <param name="Activity">The Activity to be searched in the tbl_activity table.</param>
    /// <returns></returns>
    private bool IsExistActivityId(string Activity)
    {
        string sql = "";
        sql = "select count(*) from tbl_MajorActivity where name='" + Activity.Trim() + "'";

        try
        {
            DataSet ds = mgr.ExecuteQuery(sql, "tblActivity");
            int count = (int)ds.Tables[0].Rows[0][0];
            if (count > 0)
                return true;
            else
                return false;
        }
        catch { return false; }
    }

    /// <summary>
    /// Get the Activity Id based for the Activity supplied
    /// </summary>
    /// <param name="Activity">Contains the text of the activity </param>
    /// <returns></returns>
    private string GetActivityId(string Activity)
    {
        if (Activity.Trim().Length <= 0)
        { return "0"; }

        string sql = "";
        sql = "select id from tbl_MajorActivity where name='" + Activity.Trim() + "'";

        try
        {
            DataSet ds = mgr.ExecuteQuery(sql, "tblActivity");
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
        }
        catch { return "0"; }

        return "0";
    }

    /// <summary>
    /// Call a stored procedure which inserts/updates the Medical School and returns the new or updated School ID
    /// </summary>
    /// <param name="schname"></param>
    /// <returns>School ID</returns>
    /// <remarks>
    /// Author  : unknown
    /// Edit    : Kajal Mandal
    /// Date    : 13 Jan 2007
    /// </remarks>    
    public int insert_medicalschool(string schname)
    {
        //QUERY HAS BEEN REPLACED BY SQL STORED PROCEDURE, K: 13 JAN 2007

        SqlParameter[] prm = new SqlParameter[2];
        prm[0] = new SqlParameter("@MedicalSchool", SqlDbType.VarChar, 500);
        prm[1] = new SqlParameter("@SchoolID", SqlDbType.Int);
        prm[0].Value = mgr.FixQuote(schname);
        prm[1].Direction = ParameterDirection.Output;

        try
        {
            mgr.ExecuteUpdate("Upload_doctor_Medicalschool", prm);
            return Convert.ToInt32(prm[1].Value);
        }
        catch (Exception ex)
        {
            lblMsg.Visible = true;
            lblMsg.Text = ex.ToString();
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return 0;
        }
        ds_common = mgr.ExecuteQuery("GET_SCH_HOS_ORG_DEP", "tbl_common");
    }

    /// <summary>
    /// Call a stored procedure which inserts/updates the department and returns the new or updated ID
    /// </summary>
    /// <param name="schname"></param>
    /// <returns>Department ID</returns>
    /// <remarks>
    /// Author  : Kajal Mandal
    /// Date    : 19 Jan 2007
    /// </remarks>
    public int insert_Hospital(string hospital_name)
    {
        SqlParameter[] prm = new SqlParameter[2];
        prm[0] = new SqlParameter("@HOSPITAL_NAME", SqlDbType.VarChar, 500);
        prm[1] = new SqlParameter("@HOSPITAL_ID", SqlDbType.Int);
        prm[0].Value = mgr.FixQuote(hospital_name);
        prm[1].Direction = ParameterDirection.Output;

        try
        {
            mgr.ExecuteUpdate("ADD_UPDATE_BULK_HOSPITAL", prm);
            return Convert.ToInt32(prm[1].Value);
        }
        catch (Exception ex)
        {
            lblMsg.Visible = true;
            lblMsg.Text = ex.ToString();
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return 0;
        }
    }

    /// <summary>
    /// Call a stored procedure which inserts/updates the organization and returns the new or updated ID
    /// </summary>
    /// <param name="schname"></param>
    /// <returns>Organization ID</returns>
    /// <remarks>
    /// Author  : Kajal Mandal
    /// Date    : 19 Jan 2007
    /// </remarks>
    public int insert_Organization(string org_name)
    {
        SqlParameter[] prm = new SqlParameter[2];
        prm[0] = new SqlParameter("@Orgnization", SqlDbType.VarChar, 200);
        prm[1] = new SqlParameter("@OrgID", SqlDbType.Int);
        prm[0].Value = mgr.FixQuote(org_name);
        prm[1].Direction = ParameterDirection.Output;

        try
        {
            mgr.ExecuteUpdate("UPLOAD_DOCTOR_ORGANIZATION", prm);
            return Convert.ToInt32(prm[1].Value);
        }
        catch (Exception ex)
        {
            lblMsg.Visible = true;
            lblMsg.Text = ex.ToString();
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return 0;
        }
    }

    /// <summary>
    /// Call a stored procedure which inserts/updates the department and returns the new or updated ID
    /// </summary>
    /// <param name="schname"></param>
    /// <returns>Department ID</returns>
    /// <remarks>
    /// Author  : Kajal Mandal
    /// Date    : 19 Jan 2007
    /// </remarks>
    public int insert_Department(string department_name)
    {
        SqlParameter[] prm = new SqlParameter[2];
        prm[0] = new SqlParameter("@Department", SqlDbType.VarChar, 80);
        prm[1] = new SqlParameter("@DeptID", SqlDbType.Int);
        prm[0].Value = mgr.FixQuote(department_name);
        prm[1].Direction = ParameterDirection.Output;

        try
        {
            mgr.ExecuteUpdate("UPLOAD_DOCTOR_DEPARTMENT", prm);
            return Convert.ToInt32(prm[1].Value);
        }
        catch (Exception ex)
        {
            lblMsg.Visible = true;
            lblMsg.Text = ex.ToString();
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return 0;
        }
    }


    protected void grdErrors_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                e.Row.Cells[i].Text = Server.HtmlDecode(e.Row.Cells[i].Text);

                try
                {
                    //displaying the error messages tooltip for the rows,columns

                    e.Row.Cells[i].ToolTip = dtDoctors_Err.Rows[e.Row.RowIndex].GetColumnError(i);
                    if (dtDoctors_Err.Rows[e.Row.RowIndex].GetColumnError(i).Equals("The value of this column can not be empty!!"))
                    {
                        e.Row.Cells[i].BackColor = System.Drawing.Color.Red;
                        e.Row.Cells[i].ForeColor = System.Drawing.Color.Red;
                    }

                }
                catch { }
            }
        }
    }

}
